package com.swan.models;

import java.io.Closeable;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.function.Function;

import com.swan.exceptions.CustomerException;

public class DBUtil implements Closeable {

    public static final String DRIVER;
    public static final String URL;
    private static final String USER;
    private static final String PWD;

    static {
        try {
            /** load db connection information from configuration file.
             * we can encrypt connection information,user,password later if it is needed.
             */
            Properties p = new Properties();
            p.load(DBUtil.class.getResourceAsStream("/db.properties"));
            DRIVER = p.getProperty("DRIVER");
            URL = p.getProperty("URL");
            USER = p.getProperty("USER");
            PWD = p.getProperty("PWD");
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            throw new CustomerException(e);
        } catch (IOException e) {
            throw new CustomerException(e);
        }
    }

    private Connection conn = null;
    private PreparedStatement pst = null;

    public DBUtil() {
        try {
            this.conn = DriverManager.getConnection(URL, USER, PWD);
            conn.setAutoCommit(false);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public Connection getConn() {
        return conn;
    }

    /**
     * Use <b>PreparedStatement</b> query data.
     * Value must be <b>String</b> or <b>Integer</b> type.
     *
     * @param sql    sql pattern that will be run
     * @param values values for sql pattern
     * @return <b>ResultSet</b> object
     * @throws SQLException
     */
    public ResultSet preparedStatement(String sql, Object... values) throws SQLException {
        pst = conn.prepareStatement(sql);
        setParameter(values);
        return pst.executeQuery();
    }

    private void setParameter(Object[] values) throws SQLException {
        int i = 1;
        for (Object obj : values) {
            if (obj instanceof String)
                pst.setString(i++, (String) obj);
            else if (obj instanceof Integer)
                pst.setInt(i++, (Integer) obj);
            else
                throw new CustomerException(i + "参数设置不正确");
        }
    }

    /**
     * Use <b>PreparedStatement</b> update data.
     * Value must be <b>String</b> or <b>Integer</b> type.
     *
     * @param sql    sql pattern that will be run
     * @param mode   mode for return generate key
     * @param values values for sql pattern
     * @return integer value that the row number of execute sql
     * @throws SQLException
     * @See {@link Statement#RETURN_GENERATED_KEYS}
     */
    public int updateWithMode(String sql, int mode, Object... values) {
        try {
            pst = conn.prepareStatement(sql, mode);
            setParameter(values);
            return pst.executeUpdate();
        } catch (SQLException exception) {
            throw new CustomerException(exception);
        }
    }

    public int updateWithoutGeneratedKey(String sql, Object... values) {
        return updateWithMode(sql, Statement.NO_GENERATED_KEYS, values);
    }

    /**
     * Repeat update data.
     *
     * @param values values for sql pattern
     * @return integer value that the row number of execute sql
     * @throws SQLException
     */
    public int repeat(Object... values) throws SQLException {
        int i = 1;
        for (Object obj : values) {
            if (obj instanceof String)
                pst.setString(i++, (String) obj);
            else if (obj instanceof Integer)
                pst.setInt(i++, (Integer) obj);
        }
        return pst.executeUpdate();
    }

    int getGeneratedKey() {
        try {
            ResultSet rs = pst.getGeneratedKeys();
            if (rs.next())
                return rs.getInt(1);
        } catch (SQLException ex) {
            throw new CustomerException(ex);
        }
        return -1;
    }

    @Override
    public void close() {
        try {
            if (pst != null && !pst.isClosed()) {
                pst.close();
            }
            if (conn != null && !conn.isClosed()) {
                conn.commit();
                conn.close();
            }
        } catch (SQLException e) {
            throw new CustomerException(e);
        }
    }

    public static final <T> T doSomething(Function<DBUtil, T> function) {
        try (DBUtil db = new DBUtil()) {
            return function.apply(db);
        }
    }
}
